package manage.utils;

import manage.model.Student;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class SqliteDb {

    // 连接数据库，返回一个连接对象。
    private Connection connect() {
        try {
            // 加载驱动，jar包中的JDBC类
            Class.forName("org.sqlite.JDBC");
            // 连接数据库，不论什么数据库都是用这个方法连接，只是用的驱动包不同。
            // url jdbc:subprotocol:subname
            Connection conn = DriverManager.getConnection("jdbc:sqlite:src/manage/db/stu.db");
            System.out.println("连接数据库成功。");
            return conn;
        } catch (Exception e) {
            System.out.println("连接数据库失败，异常信息为：" + e.getMessage());
            return null;
        }
    }

    // 断开连接
    private void disconnect(Connection conn) {
        try {
            conn.close();
        } catch (Exception e) {
            System.out.println("断开连接失败，异常信息为：" + e.getMessage());
        }
    }

    // 执行sql语句：添加、修改、删除学生，显示影响了多少条数据
    private boolean execute(Connection conn, String sql) {
        boolean ret = false;

        /*
        执行sql语句，用的Statement类
        建立到数据库：铺路
        执行sql：路上跑的一辆车
         */
        try {
            Statement st = conn.createStatement();

            int count = st.executeUpdate(sql);
            System.out.println("执行sql语句成功：" + sql + "，影响了" + count + "条数据。");
            if (count >= 1) {
                return true;
            }
        } catch (Exception e) {
            System.out.println("执行sql语句异常：" + sql + "，异常信息为：" + e.getMessage());
        }


        return ret;
    }

    // 执行sql语句：查询类的操作，显示查到的学生信息
    private List<Student> executeQuery(Connection conn, String sql) {
        List<Student> students = new ArrayList<>();

        try {
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            if (rs != null) { // 查询结果不为空
                while (rs.next()) {
                    int id = rs.getInt("id"); // 根据列名取对应的值
                    String name = rs.getString("name");
                    String gender = rs.getString("gender");
                    String age = rs.getString("age");
                    String classes = rs.getString("classes");
                    String phone = rs.getString("phone");
                    String score = rs.getString("score");

                    // 创建学生对象
                    Student stu = new Student(id, name, age, gender, classes, phone, score);
                    // 将学生加入到列表中
                    students.add(stu);
                }
            }

        } catch (Exception e) {
            System.out.println("执行sql语句异常：" + sql + "，异常信息为：" + e.getMessage());
        }

        return students;
    }

    /*
    创建表的方法。
    sql语句不暴露给外部
    调用起来简单
     */
    public static void initTable() {
        SqliteDb sqliteDb = new SqliteDb();
        Connection conn = sqliteDb.connect();

        // 创建表
        sqliteDb.execute(conn, "create table if not exists student(id INTEGER primary key AUTOINCREMENT, " +
                "name varchar(8), gender varchar(8), age varchar(8), " +
                "classes varchar(8), score varchar(8), phone varchar(16));");

        sqliteDb.disconnect(conn);
    }

    /*
    添加学生
     */
    public static boolean addStudent(Student student) {
        int id = student.getId();
        String name = student.getName();
        String age = student.getAge();
        String gender = student.getGender();
        String classes = student.getClasses();
        String phone = student.getPhone();
        String score = student.getScore();

        SqliteDb sqliteDb = new SqliteDb();
        Connection conn = sqliteDb.connect();

        String sql = String.format("insert into student (name,gender,age,classes,score,phone) " +
                        "values ('%s', '%s', '%s', '%s', '%s', '%s');",
                name, gender, age, classes, score, phone);

        boolean bool = sqliteDb.execute(conn, sql);

        sqliteDb.disconnect(conn);

        return bool;
    }

    /*修改学生*/
    public static boolean modifyStudent(Student student) {
        int id = student.getId();
        String name = student.getName();
        String age = student.getAge();
        String gender = student.getGender();
        String classes = student.getClasses();
        String phone = student.getPhone();
        String score = student.getScore();

        SqliteDb sqliteDb = new SqliteDb();
        Connection conn = sqliteDb.connect();

        String sql = String.format("update student set name='%s',gender='%s'," +
                        "age='%s',classes='%s',score='%s',phone='%s' where id='%s'",
                name, gender, age, classes, score, phone, id);

        boolean bool = sqliteDb.execute(conn, sql);

        sqliteDb.disconnect(conn);

        return bool;
    }

    /*删除学生*/
    public static boolean deleteStudent(int id) {
        SqliteDb sqliteDb = new SqliteDb();
        Connection conn = sqliteDb.connect();
        String sql = String.format("delete from student where id='%s'", id);
        boolean bool = sqliteDb.execute(conn, sql);
        sqliteDb.disconnect(conn);

        return bool;
    }

    /*全量查找学生*/
    public static List<Student> queryStudent() {
        List<Student> students = new ArrayList<>();

        SqliteDb sqliteDb = new SqliteDb();
        Connection conn = sqliteDb.connect();
        String sql = "select * from student";
        students = sqliteDb.executeQuery(conn, sql);
        sqliteDb.disconnect(conn);

        return students;
    }

    /*根据关键字模糊查找学生*/
    public static List<Student> queryStudent(String key) {
        List<Student> students = new ArrayList<>();

        SqliteDb sqliteDb = new SqliteDb();
        Connection conn = sqliteDb.connect();
        // 姓名中包含男、性别包含男，班级包含男都能匹配出来。李男生 %男% 前后都能模糊
        // select * from student where name like '%男%' or gender like '%男%' or classes like '%男%'
        String sql = String.format("select * from student where name like '%%%s%%' " +
                "or gender like '%%%s%%' or classes like '%%%s%%'", key, key, key);
        students = sqliteDb.executeQuery(conn, sql);
        sqliteDb.disconnect(conn);

        return students;
    }


    // 测试代码，用完删除
    public static void main(String[] args) {
        /*SqliteDb sqliteDb = new SqliteDb();
        Connection conn = sqliteDb.connect();

        // 创建表
        sqliteDb.execute(conn, "create table if not exists student(id INTEGER primary key AUTOINCREMENT, " +
                "name varchar(8), gender varchar(8), age varchar(8), " +
                "classes varchar(8), score varchar(8), phone varchar(16));");

        // 添加学生
        sqliteDb.execute(conn, "insert into student (name,gender,age,classes,score,phone) " +
                "values ('Tom', '男', '18', '一年级1班', '99', '18012345678');");

        List<Student> s = sqliteDb.executeQuery(conn, "select * from student;");
        System.out.println(s);

        sqliteDb.disconnect(conn);*/
        SqliteDb.initTable();

        Student stu1 = new Student(1, "Lily", "18", "女",
                "一年级1班", "18012345678", "99");
        Student stu2 = new Student(2, "Jack", "18", "男",
                "一年级1班", "18012345678", "99");
        Student stu3 = new Student(3, "李一男", "18", "",
                "一年级5班", "18012345678", "99");
        Student stu4 = new Student(4, "Lucy", "18", "",
                "一年级男子班", "18012345678", "99");
        System.out.println(SqliteDb.addStudent(stu1));
        System.out.println(SqliteDb.addStudent(stu2));
        System.out.println(SqliteDb.addStudent(stu3));
        System.out.println(SqliteDb.addStudent(stu4));

        List<Student> students = SqliteDb.queryStudent();
        System.out.println(students);
        System.out.println(students.size());

        stu3.setGender("女");
        SqliteDb.modifyStudent(stu3);

        students = SqliteDb.queryStudent();
        System.out.println(students);

        students = SqliteDb.queryStudent("男");
        System.out.println(students);
        System.out.println(students.size());

//        SqliteDb.deleteStudent(1);
//        SqliteDb.deleteStudent(2);
//        SqliteDb.deleteStudent(3);
//        SqliteDb.deleteStudent(4);

    }
}
